Click Here!
home account info subscribe login search My ITKnowledge FAQ/help site map contact us


 
Brief Full
 Advanced
      Search
 Search Tips
To access the contents, click the chapter and section titles.

Oracle Performance Tuning and Optimization
(Publisher: Macmillan Computer Publishing)
Author(s): Edward Whalen
ISBN: 067230886x
Publication Date: 04/01/96

Bookmark It

Search this book:
 
Previous Table of Contents Next


How Procedures and Functions Operate

Procedures and functions use the same basic syntax in the program body with the exception of the RETURN keyword that can only be used by functions. The body itself is made up of PL/SQL blocks that perform the desired function and return the desired data to the calling program. The goal of the body of the procedure is both to minimize the amount of data to be transmitted across the network (to and from the calling program) and to perform these PL/SQL statements in the most efficient manner possible.

The PL/SQL Language

PL/SQL is a block-structured language offered by Oracle to facilitate the use of the Oracle RDBMS. PL/SQL has the following properties and features that can be used to aid in application development:


Feature Description

Block structure The PL/SQL language is a block-structured language that allows blocks to contain nested subblocks.
Block declarations Each block can have its own declarations, which means that you can logically separate functions.
Variable declaration Variables can be declared and used within a PL/SQL block.
Constant declaration Constants can be declared and referenced within a PL/SQL block.
Conditional statements PL/SQL allows for conditional processing with IF...THEN...ELSE, WHILE...LOOP, FOR...LOOP, EXIT...WHEN, and GOTO functions.

These features make PL/SQL a powerful SQL processing language. The use of PL/SQL has several major advantages over the use of SQL statements (in addition to stored procedures and functions). Among these are ease of use, portability, and higher performance.

The primary performance difference between PL/SQL and SQL is the fact that PL/SQL statements are transmitted to Oracle as a block of statements rather than as individual statements. In a network application, the additional overhead needed to transmit individual statements can be quite high. It takes very little more CPU and network resources to send a larger packet than it does to send a smaller one.

The RETURN Statement

In the declaration portion of a function, a RETURN parameter is used to declare the type of the return value. Later, in the body of the function, the RETURN statement is used to exit the function and return the specified value to the calling program. With a procedure, the RETURN statement can also be used, but not to return a value. In a procedure, the RETURN statement can be used only to exit the procedure. No values can be associated with the RETURN statement in a procedure.

The EXCEPTION Statement

In both procedures and functions, you can add optional exception handlers. These exception handlers allow you to return additional information based on certain conditions (such as no data found or some user-specified condition). By using exception handlers and allowing the stored procedure to notify you of some special conditions, you can minimize the amount of return-value checking that must be done in the application code. Because the work to determine that no data has been selected has already been done by the RDBMS engine, you can save on resources if you take advantage of this information.

The RDBMS_OUTPUT Package

To visually represent data selected within a stored procedure or function, you can use the RDBMS_OUTPUT package supplied by Oracle. To see data returned by RDBMS_OUTPUT in SQL*Plus or Server Manager, you must set the SERVEROUTPUT option. When using the RDBMS_OUTPUT package, you can select several options for inputting or outputting data. The following procedures are available in the RDBMS_OUTPUT package:


Procedure Description

RDBMS_OUTPUT.ENABLE Enables output processing.
RDBMS_OUTPUT.DISABLE Disables output processing.
RDBMS_OUTPUT.PUT_LINE Places a newline-terminated string in the buffer.
RDBMS_OUTPUT.PUT Places a string in the buffer (no newline).
RDBMS_OUTPUT.GET_LINE Gets one line from the buffer.
RDBMS_OUTPUT.GET_LINES Gets an array of lines from the buffer.

In this manner, you can use a stored procedure for ad-hoc functions that require data to be displayed in SQL*Plus. The typical stored procedure is used to return data that has been bound to variables in a program.


Previous Table of Contents Next


Products |  Contact Us |  About Us |  Privacy  |  Ad Info  |  Home

Use of this site is subject to certain Terms & Conditions, Copyright © 1996-2000 EarthWeb Inc.
All rights reserved. Reproduction whole or in part in any form or medium without express written permission of EarthWeb is prohibited.